<?php
class Shinkensaku_Model_Selcity extends Shinkensaku_Model_Base{

    /**
     * sql文生成
     * @param $params
     * @param $db_params
     * @return sql文
     */
	protected function generateSql($params, &$db_params)
	{
        // 都道府県コードに基づく条件文作成
        $this->setCondPrefCd($params);

$sql = <<< DOC_END

SELECT
            M43.M43_TODOUFUKEN AS M43_TODOUFUKEN
            ,T1.T57_ENSEN_SHIKUCHOUSON_CNT AS REC_CNT
            ,M43.M43_TODOUFUKEN_CD AS TODOUFUKEN_CD
            ,TRIM(M43.M43_TODOUFUKEN_CD || T1.T57_ENSEN_SHIKUCHOUSON_CD) AS SHIKUCHOUSON_CD
            ,T1.T57_ENSEN_SHIKUCHOUSON_NAME AS SHIKUCHOUSON
            ,NVL(T2.PREF_HEYA_CNT, 0) AS PREF_HEYA_CNT
        FROM
            (
                SELECT
                        T57_TODOUFUKEN_CD
                        ,T57_ENSEN_SHIKUCHOUSON_CD
                        ,T57_ENSEN_SHIKUCHOUSON_NAME
                        ,T57_ENSEN_SHIKUCHOUSON_CNT
                    FROM
                        T57ENSEN_SHIKUCHOUSON_SUM
                    WHERE
                        {$this->strCheckSQLT57}
                        	T57_ENSEN_SHIKUCHOUSON_FLAG = 1
                        AND T57_DATA_KUBUN = 0
            ) T1
            ,(
                SELECT
                        SUM(T57_ENSEN_SHIKUCHOUSON_CNT) AS PREF_HEYA_CNT
                        ,T57_TODOUFUKEN_CD
                    FROM
                        T57ENSEN_SHIKUCHOUSON_SUM
                    WHERE
                        {$this->strCheckSQLT57}
                        	T57_ENSEN_SHIKUCHOUSON_FLAG = 1
                        AND T57_DATA_KUBUN = 0
                    GROUP BY
                        T57_TODOUFUKEN_CD
            ) T2
            ,(
                SELECT
                        M43_TODOUFUKEN_CD
                        ,M43_TODOUFUKEN
                    FROM
                        M43TODOUFUKEN
                        {$this->strCheckSQLM43}
            ) M43
        WHERE
            M43.M43_TODOUFUKEN_CD = T1.T57_TODOUFUKEN_CD(+)
            AND M43.M43_TODOUFUKEN_CD = T2.T57_TODOUFUKEN_CD(+)
        ORDER BY
            M43_TODOUFUKEN_CD ASC
            ,SHIKUCHOUSON_CD ASC

DOC_END;

        return $sql;
	}

    /**
     * sql文生成（OEM）
     * @param $params
     * @param $db_params
     * @return sql文
     */
	protected function generateOemSql($params, &$db_params)
    {
    	// OemCdに基づく条件文を生成
        $strWhereOemM05 = $this->generateCondOem($params);
        // 都道府県コードに基づく条件文作成
        $this->setCondPrefCd($params);

        $sql .= "";
        $sql .= " SELECT";
		$sql .= "         C.T03_TODOUFUKEN_CD AS TODOUFUKEN_CD";
		$sql .= "         ,C.M01_TODOUFUKEN AS M43_TODOUFUKEN";
		$sql .= "         ,TRIM(C.T03_TODOUFUKEN_CD || C.T03_SHIKUCHOUSON_CD) AS SHIKUCHOUSON_CD";
		$sql .= "         ,C.M01_SHIKUCHOUSON AS SHIKUCHOUSON";
		$sql .= "         ,C.REC_CNT AS REC_CNT";
		$sql .= "         ,D.REC_CNT AS PREF_HEYA_CNT";
		$sql .= "     FROM";
		$sql .= "         (";
		$sql .= "             SELECT";
		$sql .= "                     T03_TODOUFUKEN_CD";
		$sql .= "                     ,T03_SHIKUCHOUSON_CD";
		$sql .= "                     ,M01_TODOUFUKEN";
		$sql .= "                     ,M01_SHIKUCHOUSON";
		$sql .= "                     ,COUNT(*) AS REC_CNT";
		$sql .= "                 FROM";
		$sql .= "                     (";
		$sql .= "                         SELECT";
		$sql .= "                                 T03_TODOUFUKEN_CD";
		$sql .= "                                 ,T03_SHIKUCHOUSON_CD";
		$sql .= "                                 ,T03_CHOUMEI_CD";
		$sql .= "                                 ,T03_CHOUMOKU_CD";
		$sql .= "                                 ,T03_ORIGINAL_TATEMONO_CD01";
		$sql .= "                                 ,T03_ORIGINAL_HEYA_CD01";
		$sql .= "                                 ,T03_KASHIDASHI_KUBUN";
		$sql .= "                             FROM";
		$sql .= "                                 T03WEB_HEYA";
		$sql .= "                                 ,M05TENPO_INFO";
		$sql .= "                             WHERE";
		$sql .=                                   $this->strCheckSQLT03;
		$sql .= "                                 M05_TENPO_CD = T03_TENPO_CD";
    	if(!empty($params['shikireizero']) && $params['shikireizero'] == "on"){
			$sql .= "                             AND T03_KASHIDASHI_KUBUN = 1 ";
		}
		$sql .=                                   $strWhereOemM05;
		$sql .= "                     )";
		$sql .= "                     ,M01JYUUSHO";
		$sql .= "                 WHERE";
		$sql .= "                     T03_TODOUFUKEN_CD = M01_TODOUFUKEN_CD";
		$sql .= "                     AND T03_SHIKUCHOUSON_CD = M01_SHIKUCHOUSON_CD";
		$sql .= "                     AND T03_CHOUMEI_CD = M01_CHOUMEI_CD";
		$sql .= "                     AND T03_CHOUMOKU_CD = M01_CHOUMOKU_CD";
		$sql .= "                 GROUP BY";
		$sql .= "                     T03_TODOUFUKEN_CD";
		$sql .= "                     ,T03_SHIKUCHOUSON_CD";
		$sql .= "                     ,M01_TODOUFUKEN";
		$sql .= "                     ,M01_SHIKUCHOUSON";
		$sql .= "         ) C";
		$sql .= "         ,(";
		$sql .= "             SELECT";
		$sql .= "                     T03_TODOUFUKEN_CD";
		$sql .= "                     ,COUNT(*) AS REC_CNT";
		$sql .= "                 FROM";
		$sql .= "                     (";
		$sql .= "                         SELECT";
		$sql .= "                                 T03_TODOUFUKEN_CD";
		$sql .= "                                 ,T03_ORIGINAL_TATEMONO_CD01";
		$sql .= "                                 ,T03_ORIGINAL_HEYA_CD01";
		$sql .= "                                 ,T03_KASHIDASHI_KUBUN";
		$sql .= "                             FROM";
		$sql .= "                                 T03WEB_HEYA";
		$sql .= "                                 ,M05TENPO_INFO";
		$sql .= "                             WHERE";
		$sql .=                                   $this->strCheckSQLT03;
		$sql .= "                                 M05_TENPO_CD = T03_TENPO_CD";
		if(!empty($params['shikireizero']) && $params['shikireizero'] == "on"){
			$sql .= "                             AND T03_KASHIDASHI_KUBUN = 1 ";
		}
		$sql .=                                  $strWhereOemM05;
		$sql .= "                     )";
		$sql .= "                 GROUP BY";
		$sql .= "                     T03_TODOUFUKEN_CD";
		$sql .= "         ) D";
		$sql .= "     WHERE";
		$sql .= "         C.T03_TODOUFUKEN_CD = D.T03_TODOUFUKEN_CD";
		$sql .= "     ORDER BY";
		$sql .= "         C.T03_TODOUFUKEN_CD ASC";
		$sql .= "         ,TRIM(C.T03_TODOUFUKEN_CD || C.T03_SHIKUCHOUSON_CD) ASC";

		return $sql;
    }
}
?>